![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
Avoid Dynamic Growth As stated earlier, you want to avoid the dynamic space management that causes additional overhead and transactional delays. To determine whether rollback segments are a problem, look in the dynamic performance table, V$ROLLSTAT. The following columns are of particular interest:
You can look at these statistics by using a SQL statement such as this one: SQL> SELECT substr(name,1,40), extents, rssize, aveactive, aveshrink, extends, shrinks 2 FROM v$rollname rn, v$rollstat rs 3 WHERE rn.usn = rs.usn; SUBSTR(NAME,1,40) EXTENTS RSSIZE AVEACTIVE AVESHRINK EXTENDS SHRINKS ------------------------------ -------- -------- -------- -------- -------- SYSTEM 4 202752 0 0 0 0 RB_TEMP 53 540672 23929 0 0 0 RB1 2 202752 0 0 0 0 RB2 2 202752 55193 0 0 0 If the average size is close to the size set for OPTIMAL, OPTIMAL is set correctly. If either extends or shrinks are high, you must increase the value for OPTIMAL. Review of Rollback Segment TuningTo optimize rollback segments, remember the following rules:
Rollback segments should be monitored periodically because the applications and user activity changes. What is optimal for rollback segments today may degrade over time because of an increase in user activity or changes in application code. Checking for Latch ContentionFor a transaction to be completed, redo information must be written to the redo log. Until this write has occurred, there is danger of losing the transaction should some sort of instance failure occur. To avoid this condition, a COMMIT is not completed until the redo record has been written. Any kind of bottleneck in the redo log can cause performance problems for every process on the system. To make sure that this does not happen, check for contention on the redo log buffer latches as well as contention on the redo log buffers. Redo Log Buffer ContentionTo check for contention on the redo log buffer, simply check the dynamic performance table, V$SYSSTAT, for redo log space requests. If this number is not zero, this indicates that a process had to wait for space in the redo log buffer; the size of the redo log buffer should be increased. Check for this condition with the following SQL statement: SQL> SELECT name, value 2 FROM v$sysstat 3 WHERE name = 'redo log space requests'; NAME VALUE ---------------------------------------------------------------- -------- redo log space requests 0 If this value is not 0, increase the initialization parameter LOG_BUFFER by 5 to 10 percent until your system runs with redo log space requests close to 0.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |